package reports

import (
	"bytes"
	"database/sql"
	"encoding/json"
	"fmt"
	"strconv"
	"strings"

	"shqsoft.cn/pas3ui/base"
)

type CrossReport struct {
	ListReport
	columns []Column
}

func (cr *CrossReport) GetColumns(isAll bool) (selCols []Column, err error) {
	selCols = make([]Column, 0, len(cr.columns))
	var loop func(*Column) []Column
	loop = func(col *Column) []Column {
		clen := len(col.Children)
		if clen > 0 {
			ncol := Column{
				Title:    col.Title,
				Children: make([]Column, 0, 5),
			}
			for i := 0; i < clen; i++ {
				cs := loop(&col.Children[i])
				if len(cs) > 0 {
					ncol.Children = append(ncol.Children, cs...)
				}
			}
			if len(ncol.Children) > 0 {
				return []Column{ncol}
			}
			return nil
		}
		isHas := true
		if !isAll && !cr.colsDisplay[col.Name] {
			isHas = false
		}
		if cr.groupByIndex > 0 {
			find := false
			for _, cName := range cr.config.Groups[cr.groupByIndex-1].ExcludeCols {
				if col.Name == cName {
					find = true
					break
				}
			}
			if find {
				return nil
			}
		}
		if isHas {
			cs := make([]Column, 0, 3)
			cs = append(cs, *col)
			return cs
		}
		return nil
	}
	for _, col := range cr.columns {
		cs := loop(&col)
		if len(cs) > 0 {
			selCols = append(selCols, cs...)
		}
	}
	return
}

func (cr *CrossReport) createCrossColumns() (err error) {
	var cols []Column
	rkey := fmt.Sprintf("cols:%s", cr.tableName)
	colsStr, err := base.RedisCli.Get(ctx, rkey).Result()
	if err != nil {
		return err
	}
	err = json.Unmarshal([]byte(colsStr), &cols)
	if err != nil {
		return err
	}
	cr.columns = make([]Column, 0, len(cols)+len(cr.config.Rows))
	cr.columns = append(cr.columns, cr.config.Rows...)
	cr.columns = append(cr.columns, cols...)
	return
}
func (cr *CrossReport) CreateReptData() (err error) {
	err = cr.createReptData()
	if err != nil {
		return
	}
	err = cr.createCrossReptData()
	if err != nil {
		return
	}
	err = cr.createCrossColumns()
	if err != nil {
		return
	}
	cr.leafCols = make([]*Column, 0, 20)
	clen := len(cr.columns)
	for i := 0; i < clen; i++ {
		leafs := getLeafColumns(&cr.columns[i])
		cr.leafCols = append(cr.leafCols, leafs...)
	}
	colsDisplay := make(map[string]bool)
	for i := 0; i < len(cr.leafCols); i++ {
		cr.leafCols[i].DataIndex = cr.leafCols[i].Name
		colsDisplay[cr.leafCols[i].Name] = cr.leafCols[i].Dispaly
	}
	if len(cr.colsDisplay) == 0 {
		cr.colsDisplay = colsDisplay
	}
	return
}

func (cr *CrossReport) createCrossReptData() (err error) {
	tableName := cr.tableName + "_list"
	reptLock.Lock(cr.tableName)
	defer reptLock.Unlock(cr.tableName)
	var buff bytes.Buffer
	buff.WriteString("select distinct ")
	clen := len(cr.config.ColumnLevels) * 2
	if clen == 0 {
		err = fmt.Errorf("报表%s配置错误,ColumnLevels没有定义", cr.config.Name)
	}
	exists, err := cr.tableTxists(cr.tableName)
	if err != nil {
		return
	}
	if exists {
		return
	}
	for _, lev := range cr.config.ColumnLevels {
		if lev.IdCol == "" {
			err = fmt.Errorf("报表%s配置错误,ColumnLevels中的IdCol不能为空", cr.config.Name)
		}
		if lev.NameCol == "" {
			err = fmt.Errorf("报表%s配置错误,ColumnLevels中的NameCol不能为空", cr.config.Name)
		}
		buff.WriteString(lev.IdCol)
		buff.WriteString(",")
		buff.WriteString(lev.NameCol)
		buff.WriteString(",")
	}
	buff.Truncate(buff.Len() - 1)
	buff.WriteString(" from ")
	buff.WriteString(tableName)
	buff.WriteString(" order by ")
	for _, lev := range cr.config.ColumnLevels {
		if lev.OrderCol != "" {
			buff.WriteString(lev.OrderCol)
		} else {
			buff.WriteString(lev.IdCol)
		}
		buff.WriteString(",")
	}
	buff.Truncate(buff.Len() - 1)
	values := make([]sql.NullString, clen)
	scanArgs := make([]interface{}, clen)
	for i := 0; i < clen; i++ {
		scanArgs[i] = &values[i]
	}
	rows, err := cr.db.Raw(buff.String()).Rows()
	if err != nil {
		return
	}
	defer rows.Close()
	topIds := make([]string, 0, 5)
	idMaps := make(map[string][]string)
	idNames := make(map[string]string)
	for rows.Next() {
		rows.Scan(scanArgs...)
		for i, val := range values {
			if i == 0 && !base.InStringSlice("0:"+val.String, topIds) {
				topIds = append(topIds, "0:"+val.String)
			} else if i != 0 && i%2 == 0 {
				pid := strconv.Itoa((i-2)/2) + ":" + values[i-2].String
				value := strconv.Itoa(i/2) + ":" + val.String
				if idMaps[pid] == nil {
					idMaps[pid] = make([]string, 0, 8)
					idMaps[pid] = append(idMaps[pid], value)
				} else if i == clen-1 || !base.InStringSlice(value, idMaps[pid]) {
					idMaps[pid] = append(idMaps[pid], value)
				}
			}
			if i%2 == 1 {
				idNames[strconv.Itoa((i-1)/2)+":"+values[i-1].String] = val.String
			}
		}
	}
	var addCols func([]string, int) []Column
	cellsLen := len(cr.config.Cells)
	if cellsLen == 0 {
		err = fmt.Errorf("cross报表%s配置错误,Cells没有定义", cr.config.Name)
		return
	}
	var colsbuff bytes.Buffer
	colNames := make([]string, 0, 20)
	currVals := make([]string, len(cr.config.ColumnLevels))
	addCols = func(ids []string, lev int) (cols []Column) {
		isTop := false
		if lev == 0 {
			isTop = true
		}
		cols = make([]Column, 0, 6)
		colLev := cr.config.ColumnLevels[lev]
		idCol := colLev.IdCol
		pnames := make([]string, lev)
		var nameBuff bytes.Buffer
		for i := 0; i < lev; i++ {
			pnames[i] = cr.config.ColumnLevels[i].IdCol
			nameBuff.WriteString(pnames[i])
			nameBuff.WriteString(currVals[i])
			nameBuff.WriteString("_")
		}
		pname := nameBuff.String()
		for _, idwp := range ids {
			idx := strings.Index(idwp, ":")
			id := idwp[idx+1:]
			col := Column{
				Title:    idNames[idwp],
				Name:     idCol + id,
				Dispaly:  isTop,
				Children: make([]Column, 0, 6),
			}
			if cellsLen > 1 || len(idMaps[idwp]) != 0 {
				for _, cell := range cr.config.Cells {
					name := pname + idCol + id + "_" + cell.Name
					col.Children = append(col.Children, Column{
						Title:     cell.Title,
						Name:      name,
						GroupCalc: cell.GroupCalc,
						Filter:    cell.Filter,
						Sorter:    cell.Sorter,
						Dispaly:   isTop,
						Width:     cell.Width,
						DataType:  cell.DataType,
						Format:    cell.Format,
						Align:     cell.Align,
						Unit:      cell.Unit,
						GroupExp:  cell.GroupExp,
						DataIndex: name,
					})
					var condBuff bytes.Buffer
					for i, name := range pnames {
						condBuff.WriteString(name)
						condBuff.WriteString("=")
						condBuff.WriteString(currVals[i])
						condBuff.WriteString(" AND ")
					}
					condBuff.WriteString(idCol)
					condBuff.WriteString("=")
					condBuff.WriteString(id)
					if cell.GroupExp == "" {
						colsbuff.WriteString("sumIf(")
						colsbuff.WriteString(cell.Name)
						colsbuff.WriteString(",")
						colsbuff.WriteString(condBuff.String())
						colsbuff.WriteString(") AS ")
					} else {
						colsbuff.WriteString(strings.ReplaceAll(cell.GroupExp, "#condexp", condBuff.String()))
						colsbuff.WriteString(" AS ")
					}
					colsbuff.WriteString(name)
					colsbuff.WriteString(",")
					colNames = append(colNames, name)
				}
			} else {
				cell := cr.config.Cells[0]
				name := pname + idCol + id
				var condBuff bytes.Buffer
				for i, name := range pnames {
					condBuff.WriteString(name)
					condBuff.WriteString("=")
					condBuff.WriteString(currVals[i])
					condBuff.WriteString(" AND ")
				}
				condBuff.WriteString(idCol)
				condBuff.WriteString("=")
				condBuff.WriteString(id)
				if cell.GroupExp == "" {
					colsbuff.WriteString("sumIf(")
					colsbuff.WriteString(cell.Name)
					colsbuff.WriteString(",")
					colsbuff.WriteString(condBuff.String())
					colsbuff.WriteString(") AS ")
				} else {
					colsbuff.WriteString(strings.ReplaceAll(cell.GroupExp, "#condexp", condBuff.String()))
					colsbuff.WriteString(" AS ")
				}
				colsbuff.WriteString(name)
				colsbuff.WriteString(",")
				col.Name = name
				col.Dispaly = isTop
				col.Width = cell.Width
				col.DataType = cell.DataType
				col.Format = cell.Format
				col.Align = cell.Align
				col.DataIndex = name
				col.GroupExp = cell.GroupExp
				col.Children = nil
				colNames = append(colNames, name)
			}
			if len(idMaps[idwp]) != 0 {
				currVals[lev] = id
				cols := addCols(idMaps[idwp], lev+1)
				col.Children = append(col.Children, cols...)
			}
			cols = append(cols, col)
		}
		if lev == 0 && colLev.HasTotal {
			col := Column{
				Title:    "合计",
				Name:     "all",
				Dispaly:  true,
				Children: make([]Column, 0, 6),
			}
			if cellsLen > 1 {
				for _, cell := range cr.config.Cells {
					name := "all_" + cell.Name
					col.Children = append(col.Children, Column{
						Title:     cell.Title,
						Name:      name,
						GroupCalc: cell.GroupCalc,
						Filter:    cell.Filter,
						Sorter:    cell.Sorter,
						Dispaly:   true,
						Width:     cell.Width,
						DataType:  cell.DataType,
						Format:    cell.Format,
						Align:     cell.Align,
						GroupExp:  cell.GroupExp,
						DataIndex: name,
					})
					if cell.GroupExp == "" {
						colsbuff.WriteString("sum(")
						colsbuff.WriteString(cell.Name)
						colsbuff.WriteString(") AS ")
					} else {
						colsbuff.WriteString(cell.GroupExp)
						colsbuff.WriteString(" AS ")
					}
					colsbuff.WriteString(name)
					colsbuff.WriteString(",")
					colNames = append(colNames, name)
				}
			} else {
				cell := cr.config.Cells[0]
				name := "all_" + cell.Name
				if cell.GroupExp == "" {
					colsbuff.WriteString("sum(")
					colsbuff.WriteString(cell.Name)
					colsbuff.WriteString(") AS ")
				} else {
					colsbuff.WriteString(cell.GroupExp)
					colsbuff.WriteString(" AS ")
				}
				colsbuff.WriteString(name)
				colsbuff.WriteString(",")
				col.Name = name
				col.Dispaly = true
				col.Width = cell.Width
				col.DataType = cell.DataType
				col.Format = cell.Format
				col.Align = cell.Align
				col.DataIndex = name
				col.GroupExp = cell.GroupExp
				col.Children = nil
				colNames = append(colNames, name)
			}
			cols = append(cols, col)
		}
		return cols
	}
	cr.columns = addCols(topIds, 0)
	rkey := fmt.Sprintf("cols:%s", cr.tableName)
	colBytes, err := json.Marshal(cr.columns)
	if err != nil {
		return err
	}
	err = base.RedisCli.Set(ctx, rkey, string(colBytes), 0).Err()
	if err != nil {
		return err
	}
	var sqlBuff bytes.Buffer
	sqlBuff.WriteString("create table ")
	sqlBuff.WriteString(cr.tableName)
	sqlBuff.WriteString("(")
	for _, col := range cr.config.Rows {
		sqlBuff.WriteString(col.Name)
		sqlBuff.WriteString(" ")
		sqlBuff.WriteString(getColDBType(cr.config.DataSource.DbName, col.DataType, col.Width))
		sqlBuff.WriteString(",")
	}
	for _, colName := range colNames {
		sqlBuff.WriteString(colName)
		sqlBuff.WriteString(" Float64,")
	}
	sqlBuff.WriteString("gp_index UInt8")
	sqlBuff.WriteString(") ENGINE = Memory")
	err = cr.db.Exec(sqlBuff.String()).Error
	if err != nil {
		return
	}
	var insBuff bytes.Buffer
	insBuff.WriteString("insert into ")
	insBuff.WriteString(cr.tableName)
	insBuff.WriteString("(")
	for _, col := range cr.config.Rows {
		insBuff.WriteString(col.Name)
		insBuff.WriteString(",")
	}
	for _, colName := range colNames {
		insBuff.WriteString(colName)
		insBuff.WriteString(",")
	}
	insBuff.WriteString("gp_index) select ")
	for _, col := range cr.config.Rows {
		if base.InStringSlice(col.Name, cr.config.KeyColumns) {
			insBuff.WriteString(col.Name)
		} else {
			insBuff.WriteString("min(")
			insBuff.WriteString(col.Name)
			insBuff.WriteString(")")
		}
		insBuff.WriteString(",")
	}
	insBuff.WriteString(colsbuff.String())
	insBuff.WriteString("gp_index from ")
	insBuff.WriteString(tableName)
	insBuff.WriteString(" group by ")
	insBuff.WriteString(strings.Join(cr.config.KeyColumns, ","))
	insBuff.WriteString(",")
	insBuff.WriteString("gp_index")
	err = cr.db.Exec(insBuff.String()).Error
	return
}

func (cr *CrossReport) tableTxists(tName string) (exists bool, err error) {
	var sqlStr string
	if cr.config.DataSource.DbName == "ch" {
		sqlStr = fmt.Sprintf("select count(*) from system.tables c where database ='%s' and name = ?", base.AppConf.CHDBName)
	} else {
		sqlStr = "select count(*) from information_schema.tables where TABLE_NAME = ? "
	}
	var count int
	err = cr.db.Raw(sqlStr, tName).Row().Scan(&count)
	if err != nil {
		return
	}
	if count > 0 {
		exists = true
	}
	return
}

//ExportXslx 导出Xslx格式
func (cr *CrossReport) ExportXslx() (buff *bytes.Buffer, name string, err error) {
	xslx, err := NewXlsx()
	if err != nil {
		return nil, "", err
	}
	selCols, _ := cr.GetColumns(false)
	xslx.AddColumns(selCols)
	var rows *sql.Rows
	var retCols []*Column
	if rows, retCols, _, err = cr.getTableDatas(-1, -1); err != nil {
		return
	}
	defer rows.Close()
	cols, err := base.GetColumns(rows)
	if err != nil {
		return nil, "", err
	}
	clen := len(cols)
	values := make([]sql.NullString, clen)
	scanArgs := make([]interface{}, clen)
	for i := 0; i < clen; i++ {
		scanArgs[i] = &values[i]
	}
	for rows.Next() {
		err := rows.Scan(scanArgs...)
		if err != nil {
			return nil, "", err
		}
		mapVal, err := cr.toMap(values, retCols, nil, -1)
		if err != nil {
			return nil, "", err
		}
		if err = xslx.AddRow(mapVal); err != nil {
			return nil, "", err
		}
	}
	if cr.config.HasTotal {
		tData, err := cr.getTotal(nil)
		if err != nil {
			return nil, "", err
		}
		if err = xslx.AddRow(tData); err != nil {
			return nil, "", err
		}
	}
	buff = xslx.Export()
	name = cr.paramsToString()
	return
}
